package ExcelToDb;

import POJO.DbMoudle;
import POJO.ExcelToDb_Excel;
import com.mongodb.MongoClient;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bson.Document;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class Main {

    static MongoCollection collection;

    public static void START(ExcelToDb_Excel excel, DbMoudle dbMoudle) throws Exception {
        InitDbInfo(dbMoudle);

        List<Document> documentList = GetDocumentList(excel);
        if (documentList.size() < 1)
            throw new Exception("document数量为0，检查excel在进行充重试！");


    }

    public static void InitDbInfo(DbMoudle dbMoudle) throws Exception {
        //logger = LoggerFactory.getLogger(Main.class);
        try{
            MongoClient client =  new MongoClient(dbMoudle.getAddress(), Integer.valueOf(dbMoudle.getPort()));
            MongoDatabase mongoDatabase= client.getDatabase(dbMoudle.getDbName());
            collection = mongoDatabase.getCollection(dbMoudle.getCollection());
        }catch (Exception e){
            e.printStackTrace();
            //logger.warn("collection 初始化错误");
            throw new Exception("collection 初始化错误，请检查信息输入是否有误！");
        }
    }

    public static File TransformToJavaFile(ExcelToDb_Excel excel) throws IOException {
        //在根目录下创建一个tempfileDir的临时文件夹
        String contextpath = "./tempfileDir";
        File f = new File(contextpath);
        if(!f.exists()){
            f.mkdirs();
        }


        //在tempfileDir的临时文件夹下创建一个新的和上传文件名一样的文件
        String filename = excel.getMultipartFile().getOriginalFilename();
        String filepath = contextpath+"/"+filename;
        File newFile = new File(filepath);


        //将MultipartFile文件转换，即写入File新文件中，返回File文件
        CommonsMultipartFile commonsmultipartfile = (CommonsMultipartFile) excel.getMultipartFile();
        commonsmultipartfile.transferTo(newFile);
        System.out.println("转换之后的文件："+newFile);


        //如果不需要File文件可删除
            /*if(file.exists()){
                file.delete();
            }*/

        return newFile;
    }

    public static List<Document> GetDocumentList(ExcelToDb_Excel excel) throws IOException, InvalidFormatException {
        Workbook workbook = WorkbookFactory.create((File) excel.getMultipartFile());
        Sheet sheet = workbook.getSheetAt(0);

        List<String> keyList = new ArrayList<>();
        Row firstRow = sheet.getRow(0);

        final int RowSize = sheet.getPhysicalNumberOfRows();
        final int CellSize = firstRow.getPhysicalNumberOfCells();

        /**
         * 第一行数据作为字段名
         */
        for (int i = 0; i < CellSize; i++) {
            keyList.add(firstRow.getCell(i) + "");
        }

        /**
         * 读取数据 建立DocumentList
         */
        List<Document> documentList = new ArrayList<>();
        for (int i = 1; i < RowSize; i++) {
            /**
             * 以行号为id 便于日后索引
             */
            Document document = new Document("_id", i);

            Row row = sheet.getRow(i);
            for (int j = 0; j < RowSize; j++) {
                document.append(keyList.get(j), row.getCell(j)+"");
            }

            documentList.add(document);
        }

        return documentList;
    }

    public static void InsertToDb(List<Document> documentList) {
        for (Document document : documentList){
            collection.insertOne(document);
        }
        System.out.println("End of InsertToDb, Number of documentList = " + documentList.size());
    }
}
